/**
 * Description:
 * @author czc
 * @date 2018年11月26日
 * @version 1.0
 */
package com.cman777.springc.poi.util;

import com.cman777.springc.common.bean.Iface.EnumIFace;
import com.cman777.springc.common.helper.DateUtil;
import com.cman777.springc.poi.annotation.ExlField;
import com.cman777.springc.poi.vo.PoiListShowVO;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

/**
 * Title: POIUtil
 * Description:
 * @author czc
 * @date 2018年11月26日
 */
public class POIUtil {
	protected static Logger logger = LoggerFactory.getLogger(POIUtil.class);
	private final static String xls = "xls";
	private final static String xlsx = "xlsx";

	/**
	 * 读入excel文件，解析后返回,只解析第一列不为空白行的行返回List
	 * @param file
	 * @throws Exception
	 */
	public static List<String[]> readExcel4MultipartFile(MultipartFile file) throws Exception{
		//检查文件
		checkFile(file);
		//获得Workbook工作薄对象
		Workbook workbook = getWorkBook(file);
		return readWorkbook(workbook);
	}

	public static List<String[]> readWorkbook(Workbook workbook){
		//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
		List<String[]> list = new ArrayList<String[]>();
		if(workbook != null){
			for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
				//获得当前sheet工作表
				Sheet sheet = workbook.getSheetAt(sheetNum);
				if(sheet == null){
					continue;
				}
				//获得当前sheet的开始行
				int firstRowNum  = sheet.getFirstRowNum();
				System.out.println("sheetNum:"+sheetNum+"firstRowNum:"+firstRowNum);
				//获得当前sheet的结束行
				int lastRowNum = sheet.getLastRowNum();
				System.out.println("sheetNum:"+sheetNum+"lastRowNum:"+lastRowNum);
				//循环除了第一行的所有行
				for(int rowNum = firstRowNum + 1;rowNum <= lastRowNum;rowNum++){
					//获得当前行
					Row row = sheet.getRow(rowNum);
					if(row == null){
						continue;
					}
					//获得当前行的开始列
					int firstCellNum = row.getFirstCellNum();
					//获得当前行的列数
					int lastCellNum = row.getPhysicalNumberOfCells();
					//一行中读取的第一个实际列的值不为空字符串才添加到返回值 避免读取到空白行
					if(StringUtils.isEmpty(getCellValue(row.getCell(firstCellNum)))){
						continue;
					}
					String[] cells = new String[row.getPhysicalNumberOfCells()];

					//循环当前行
					for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
						Cell cell = row.getCell(cellNum);
						cells[cellNum] = getCellValue(cell);
					}
					list.add(cells);
				}
			}
		}
		return list;
	}

	public static void main(String[] args) throws IOException {
		Workbook workbook = new HSSFWorkbook(new FileInputStream("D:\\红包列表.xls"));
		for (String[] strings : readWorkbook(workbook)) {
			System.out.println(Arrays.toString(strings));
		}


	}



	/**
	 * 导出Excel
	 * @param sheetName sheet名称
	 * @param title 标题
	 * @param values 内容
	 * @param wb HSSFWorkbook对象
	 * @return
	 */
	public static Workbook getWorkbook(String sheetName, String []title, String [][]values, Workbook wb){

		// 第一步，创建一个XSSFWorkbook，对应一个Excel文件
		if(wb == null){
			wb = new XSSFWorkbook();
		}

		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		Sheet sheet = wb.createSheet(sheetName);

		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		Row row = sheet.createRow(0);

		// 第四步，创建单元格，并设置值表头 设置表头居中
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 // 创建一个居中格式
		//声明列对象
		Cell cell = null;

		//创建标题
		for(int i=0;i<title.length;i++){
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
			cell.setCellStyle(style);
		}

		//创建内容
		for(int i=0;i<values.length;i++){
			row = sheet.createRow(i + 1);
			for(int j=0;j<values[i].length;j++){
				//将内容按顺序赋给对应的列对象
				row.createCell(j).setCellValue(values[i][j]);
			}
		}
		return wb;
	}
	private static void checkFile(MultipartFile file) throws IOException{
		//判断文件是否存在
		if(null == file){
			logger.error("文件不存在！");
			throw new FileNotFoundException("文件不存在！");
		}
		//获得文件名
		String fileName = file.getOriginalFilename();
		//判断文件是否是excel文件
		if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
			logger.error(fileName + "不是excel文件");
			throw new IOException(fileName + "不是excel文件");
		}
	}
	private static Workbook getWorkBook(MultipartFile file) {
		//获得文件名
		String fileName = file.getOriginalFilename();
		//创建Workbook工作薄对象，表示整个excel
		Workbook workbook = null;
		try {
			//获取excel文件的io流
			InputStream is = file.getInputStream();
			//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
			if(fileName.endsWith(xls)){
				//2003
				workbook = new HSSFWorkbook(is);
			}else if(fileName.endsWith(xlsx)){
				//2007
				workbook = new XSSFWorkbook(is);
			}
		} catch (IOException e) {
			logger.info(e.getMessage());
		}
		return workbook;
	}
	private static String getCellValue(Cell cell){
		String cellValue = "";
		if(cell == null){
			return cellValue;
		}
		//把数字当成String来读，避免出现1读成1.0的情况
		if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
			cell.setCellType(Cell.CELL_TYPE_STRING);
		}
		//判断数据的类型
		switch (cell.getCellType()){
			case Cell.CELL_TYPE_NUMERIC: //数字
				cellValue = String.valueOf(cell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_STRING: //字符串
				cellValue = String.valueOf(cell.getStringCellValue());
				break;
			case Cell.CELL_TYPE_BOOLEAN: //Boolean
				cellValue = String.valueOf(cell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_FORMULA: //公式
				cellValue = String.valueOf(cell.getCellFormula());
				break;
			case Cell.CELL_TYPE_BLANK: //空值
				cellValue = "";
				break;
			case Cell.CELL_TYPE_ERROR: //故障
				cellValue = "非法字符";
				break;
			default:
				cellValue = "未知类型";
				break;
		}
		return cellValue;
	}


	public static void setResponseHead(HttpServletResponse response, String filename){
		//response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
		//response.setHeader("Content-Disposition", "attachment;Filename=" + filename);

		filename = filename+"-"+ DateUtil.format(LocalDateTime.now(),DateUtil.DateTimeFormatEnum.DATE_TIME_FORMAT_5) + ".xls";
		response.setCharacterEncoding("UTF-8");
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		try {
			response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes(),"iso-8859-1") );// 默认Excel名称
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}

	}






	public static void exportExcel( Class cls ,List<?> lists,OutputStream os) throws IOException {

		if(!CollectionUtils.isEmpty(lists)){
			cls = lists.get(0).getClass();
		}

		Field[] fs = cls.getDeclaredFields();

		List<Field> fields = Arrays.stream(fs).filter(vv -> {
			ExlField ef = vv.getAnnotation(ExlField.class);
			if (ef != null) {
				vv.setAccessible(true);
				return true;
			}
			return false;
		}).sorted((t1, t2) -> {
			int t1Sort = t1.getAnnotation(ExlField.class).sort();
			int t2Sort = t2.getAnnotation(ExlField.class).sort();
			return t1Sort - t2Sort;
		}).collect(Collectors.toList());

		//标题
		List<String> titleList = fields.stream().map(vv -> vv.getAnnotation(ExlField.class).title()).collect(Collectors.toList());
		//写数据
		Workbook wb = new XSSFWorkbook();
		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		Sheet sheet = wb.createSheet(cls.getSimpleName());

		// 第三步，创建单元格风格，并设置值表头 设置表头居中
		CellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 // 创建一个居中格式
		//设置背景色
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
		//设置字体
		//Font redFont = wb.createFont();
		//redFont.setColor(Font.COLOR_NORMAL);

		// 第四步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		Row row = sheet.createRow(0);
		for(int i=0;i<titleList.size();i++){
			Cell cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(titleList.get(i));
		}

		for(int i=0;i<lists.size();i++){
			Object t= lists.get(i);
			Row rowValue = sheet.createRow(i+1);
			int currentCellIndex=0;
			for(int j=0;j<fields.size();j++){
				Field field =  fields.get(j);
				Cell cell = rowValue.createCell(currentCellIndex);
				try{
					if(isBaseOrStringType(field.get(t))){
						cell.setCellValue(field.get(t)==null?"":field.get(t).toString());
						currentCellIndex+=1;
					}else if(isListType(field.get(t))){
						//返回插入了多少条记录
						int resInsNum = insertListCell(field.get(t),rowValue,sheet,currentCellIndex,i==0?true:false,style);
						currentCellIndex+=resInsNum;
					}else{
						cell.setCellValue(transField(field,t));
						currentCellIndex+=1;
					}

				}catch (Exception e){
					cell.setCellValue("未获取值");
					e.printStackTrace();
				}

			}
		}

        //自适应列宽
		//sheet.autoSizeColumn(1, true);
		try {
			wb.write(os);
		}finally {
			os.close();
		}
        //FileOutputStream fos = new FileOutputStream(new File("D:/new.xls"));
       // wb.write(fos);


	}


	private static int insertListCell(Object object, Row rowValue, Sheet sheet, int currentCellIndex, boolean isFirst, CellStyle style) {
		List<PoiListShowVO> listShowVOS = (List<PoiListShowVO>)object;
        if(isFirst){
          //添加标题栏
			Row rowValueFirst = sheet.getRow(0);
			int lastCellNumPrev = rowValueFirst.getLastCellNum()-1;
			for(int i=0;i<listShowVOS.size();i++){
				Cell cellFirst = rowValueFirst.createCell(lastCellNumPrev+i);
				cellFirst.setCellStyle(style);
				cellFirst.setCellValue(listShowVOS.get(i).getTitleName());
			}
		}

        for(int i=0;listShowVOS!=null&&i<listShowVOS.size();i++){
			Cell cell = rowValue.createCell(currentCellIndex++);
			cell.setCellValue(StringUtils.isEmpty(listShowVOS.get(i).getPoiValue())?"":listShowVOS.get(i).getPoiValue());
		}

        return listShowVOS.size();

	}

	private static boolean isListType(Object object) {
		if(object==null){
			return true;
		}

        if(List.class.isAssignableFrom(object.getClass())){
			return true;
		}

        return false;

	}


	public static String transField(Field field,Object t){

		String res = "未获取数据";
		try{
			if(field.get(t)==null){
				return "";
			}
			if(field.get(t).getClass().equals(LocalDateTime.class)){
				res = DateUtil.format((LocalDateTime)field.get(t), DateUtil.DateTimeFormatEnum.DATE_TIME_FORMAT_4);
			}else if(EnumIFace.class.isAssignableFrom(field.get(t).getClass())){
				EnumIFace enumIFace = (EnumIFace)field.get(t);
				res = enumIFace.getMessage();
			}
		}catch (Exception e){
			e.printStackTrace();
		}
		return res;
	}


	public static boolean isBaseOrStringType(Object object) {
		if(object==null){
			return true;
		}
		Class className = object.getClass();
		if (className.equals(Integer.class) ||
				className.equals(Byte.class) ||
				className.equals(Long.class) ||
				className.equals(Double.class) ||
				className.equals(Float.class) ||
				className.equals(Character.class) ||
				className.equals(Short.class) ||
				className.equals(Boolean.class)||
				className.equals(String.class)) {
			return true;
		}
		return false;
	}





}
